Relational database management systems (DBMSs) allow a user to specify queries using high level declarative languages. These queries are specified in a declarative manner, where the query is set forth but no detailed algorithm of how to obtain the results is outlined. A query optimizer in DBMSs translates the declarative query in a specified algorithm called a query execution plan. An efficient query execution plan is select by query optimizers to evaluate an input query. Selection of the execution plan is performed using a cost model that estimates the resources that are needed for each alternative plan in consideration.
Cost models for query optimizers typically are quite sophisticated and complex. While the cost model can often by used to select a good query execution plan for a given query, there are situations in which the cost model does not come up with a good execution plan. Because of its complexity, it is well known that cost models can be inherently inaccurate due to several factors. For example, factors such as erroneous cardinality estimates, inaccurate calibration constants used for costing, simplified cost formulas that miss some details, state runtime parameters that affect the execution costs of queries, and the effect of concurrent query executions all can lead to errors and inaccuracies in the optimizer cost model.
It will thus be appreciated that query optimizers do not always produce optimal query execution plans. In fact, for complex queries, query optimizers may even return plans that are decisively poor In such situations, many DBMSs allow a user (typically a database administrator) to manually adjust and correct the wrong choices that led the query optimizer to select such a poor query execution plan. To aid the database administrator, DBMSs include (to varying degrees) a mechanism called query hinting. Query hinting allows database administrator to influence the choice of the best query execution plan through query hints. In general, query hints instruct the query optimizer to constrain its search space to a certain subset of execution plans. Two ways that this can be achieved by specifying an access path for a table (in other words, force the use of an index for a particular table) or by forcing the join order of the plan based on the order of the tables listed in a Structured Query Language (SQL) string.
One problem is that there is not a unified abstraction of hints within any single DBMS. The existing set of hints supported by each DBMS is largely a toolkit of techniques that are considered important and that have been incorporated in its optimizers. This is because the hints do not express certain constraints of the structure of the execution plan, such as when trying to force an early pre-aggregation below a join. In many cases, the granularity at which hints constrain the search space is either too coarse, which effectively prevents experienced database administrators from fine-tuning a poorly optimized query, or instead requires a fully specified execution plan, which might be too inflexible and tedious for all but the simplest queries.